﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Agresso.Interface.Layer.Gateway;
using Agresso.Interface.Layer.Domain;
using System.Data.SqlClient;
using Agresso.Interface.Layer.SQL;
using System.Data;
using System.Collections;

namespace Agresso.Interface.Layer.Service
{
    public class VesselService
    {
        DataBaseConnectivity m_dataBaseConectivity;
        AlertService m_alertService;
        ProfitCentreService m_ProfitCentreService;
        agldimvalueService m_agldimvalueService;

        public VesselService(DataBaseConnectivity dataBaseConectivity)
        {
            m_dataBaseConectivity = dataBaseConectivity;
            m_alertService = new AlertService();
        }
        public VesselService()
        {
            m_dataBaseConectivity = new DataBaseConnectivity();
            m_alertService = new AlertService();
            m_agldimvalueService = new agldimvalueService();
            m_ProfitCentreService = new ProfitCentreService();
        }
        //public void Insert(Vessel vessel)
        //{
        //    try
        //    {
        //        m_dataBaseConectivity.InterfaceSqlConnection.Open();
        //        SqlCommand cmd = new SqlCommand(VesselSQL.Insert(), m_dataBaseConectivity.InterfaceSqlConnection);
        //        cmd.Parameters.Add("@vessel_name", SqlDbType.NVarChar).Value = vessel.vessel_name;
        //        cmd.Parameters.Add("@vessel_code", SqlDbType.NVarChar).Value = vessel.vessel_code;
        //        cmd.Parameters.Add("@year_to_build", SqlDbType.NVarChar).Value = vessel.year_of_build;
        //        cmd.Parameters.Add("@flag", SqlDbType.NVarChar).Value = vessel.flag;
        //        cmd.Parameters.Add("@dwt", SqlDbType.NVarChar).Value = vessel.dwt;
        //        LogService.WriteLogExecuteSQLCommand(cmd);
        //        cmd.ExecuteNonQuery();
        //    }
        //    catch (Exception x)
        //    {
        //        LogService.WriteLogException(x);
        //        throw x;
        //    }
        //    finally
        //    {
        //        m_dataBaseConectivity.InterfaceSqlConnection.Close();
        //    }

        //}
        //public IList GetVesselFromSoftmar()
        //{
        //    IList result = new ArrayList();
        //    try
        //    {
        //        m_dataBaseConectivity.SoftmarSqlConnection.Open();
        //        SqlCommand cmd = new SqlCommand(VesselSQL.GetAllSoftmarVessel(), m_dataBaseConectivity.SoftmarSqlConnection);
        //        LogService.WriteLogExecuteSQLCommand(cmd);
        //        SqlDataReader rd = cmd.ExecuteReader();
        //        while (rd.Read())
        //        {
        //            VesselSoftmar v = new VesselSoftmar(rd);
        //            result.Add(v);
        //        }
        //        rd.Close();
        //    }
        //    catch (Exception x)
        //    {
        //        LogService.WriteLogException(x);
        //        throw x;
        //    }
        //    finally
        //    {
        //        m_dataBaseConectivity.SoftmarSqlConnection.Close();
        //    }
        //    return result;
        //}
        public void ValidateVesselMaster()
        {
            IList allsoftmarVessel = new ArrayList();
            try
            {
                m_dataBaseConectivity.SoftmarSqlConnection.Open();
                m_dataBaseConectivity.AgressoSqlConnection.Open();
                SqlCommand cmd = new SqlCommand(VesselSQL.GetAllSoftmarVessel(), m_dataBaseConectivity.SoftmarSqlConnection);
                LogService.WriteLogExecuteSQLCommand(cmd);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    Vessel v = new Vessel(true, rd);
                    allsoftmarVessel.Add(v);
                }
                foreach (Vessel v in allsoftmarVessel)
                {
                    SqlCommand vslAgressoCmd = new SqlCommand(VesselSQL.GetAgressoVesselByVesselName(), m_dataBaseConectivity.AgressoSqlConnection);
                    vslAgressoCmd.Parameters.Add("@vessel_name", SqlDbType.NVarChar).Value = v.vessel_name;
                    bool exist =!(vslAgressoCmd.ExecuteScalar() == null);
                    Alert alert = m_alertService.FindAlertByKey(v.vessel_name.Trim());
                    if (exist)
                    {
                        if (alert != null)
                        {
                            alert.alert_status = AlertStatus.RESOLVED;
                            m_alertService.UpdateAlertStatus(alert);
                        }
                    }
                    else
                    {
                        if (alert == null)
                            m_alertService.InsertAlert(new Alert(AlertType.VESSEL, string.Format("Could not find Vessel with name '{0}' in Agresso",
                                v.vessel_name.Trim()), AlertStatus.PENDING, v.vessel_name.Trim(), "Vessel"));
                        else
                            m_alertService.UpdateAlertStatus(alert);
                    }

                }
                rd.Close();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.SoftmarSqlConnection.Close();
                m_dataBaseConectivity.AgressoSqlConnection.Close();
            }
        }
        public IList GetAllVesselMapping()
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand(VesselSQL.GetAllVesselMapping(), m_dataBaseConectivity.InterfaceSqlConnection);
                SqlDataReader rd = cmd.ExecuteReader();
                if (rd.HasRows)
                {
                    while (rd.Read())
                    {
                        result.Add(new Vessel(false, rd));
                    }
                }
                rd.Close();
                return result;
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }
        public bool IsValidVesselMapping(string vesselName)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                m_dataBaseConectivity.SoftmarSqlConnection.Open();
                //find vessel in vessel mapping table-------------
                SqlCommand cmd = new SqlCommand(VesselSQL.GetVesselMappingByVesselName(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("@vessel_name", SqlDbType.NVarChar).Value = vesselName;
                SqlDataReader rd = cmd.ExecuteReader();
                Vessel vessel = null;
                if (rd.HasRows)
                {
                    rd.Read();
                    vessel = new Vessel(false, rd);
                }
                rd.Close();
                //if vessel did not found in Mapping Table then get from softmar vessel master and store in vessel mapping table-----------
                if (vessel == null)
                {
                    cmd.CommandText = VesselSQL.GetSoftmarVesselMasterByName();
                    cmd.Connection = m_dataBaseConectivity.SoftmarSqlConnection;
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("@vessel_name", SqlDbType.NVarChar).Value = vesselName;
                    rd = cmd.ExecuteReader();
                    if (rd.HasRows)
                    {
                        rd.Read();
                        vessel = new Vessel(true, rd);
                    }
                    else
                    {
                        throw new Exception("Can not Find Vessel in Softmar Vessel Master with name : " + vesselName + ".");
                    }
                    rd.Close();
                    //if vessel found in Softmar Vessel master then store in mapping table------------
                    if (vessel != null)
                    {
                        //Insert vessel to vessel mapping table---------
                        cmd.CommandText = VesselSQL.InsertVesselMapping();
                        cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("@vessel_name", SqlDbType.NVarChar).Value = vessel.vessel_name;
                        cmd.Parameters.Add("@vessel_code", SqlDbType.NVarChar).Value = vessel.vessel_code;
                        cmd.Parameters.Add("@year_of_build", SqlDbType.NVarChar).Value = vessel.year_of_build;
                        cmd.Parameters.Add("@flag", SqlDbType.NVarChar).Value = vessel.flag;
                        cmd.Parameters.Add("@dwt", SqlDbType.NVarChar).Value = vessel.dwt;
                        cmd.Parameters.Add("@charter_type", SqlDbType.NVarChar).Value = vessel.charter_type;
                        cmd.Parameters.Add("@size_class", SqlDbType.NVarChar).Value = vessel.size_class;
                        cmd.Parameters.Add("@vessel_type", SqlDbType.NVarChar).Value = vessel.vessel_type;
                        cmd.Parameters.Add("@created_date", SqlDbType.DateTime).Value = DateTime.Now;
                        cmd.Parameters.Add("@modified_date", SqlDbType.DateTime).Value = DateTime.Now;
                        cmd.ExecuteNonQuery();
                        
                        //get vessel from agresso-------------
                        //cmd.CommandText = AgressoSQL.GetAglDimValueVesselByDescription();
                        //cmd.Connection = m_dataBaseConectivity.AgressoSqlConnection;
                        //cmd.Parameters.Clear();
                        //cmd.Parameters.Add("@description", SqlDbType.NVarChar).Value = vessel.vessel_name;
                        //rd = cmd.ExecuteReader();
                        //if (rd.HasRows)
                        //{
                        //    rd.Read();
                        //    agldimvalue agldim = new agldimvalue(rd);
                        //    vessel.agr_description = agldim.description;
                        //    vessel.agr_dim_value = agldim.dim_value;
                        //    vessel.agr_rel_value = agldim.rel_value;
                        //}
                        //rd.Close();
                    }
                }
                return vessel == null ? false : vessel.agr_description != string.Empty;
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
                m_dataBaseConectivity.SoftmarSqlConnection.Close();
            }
            return false;
        }
        public Vessel GetVesselMapping(string vesselName, string key, AlertType alertType)
        {
            Vessel vessel = null;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                m_dataBaseConectivity.SoftmarSqlConnection.Open();
                //find vessel in vessel mapping table-------------
                SqlCommand cmd = new SqlCommand(VesselSQL.GetVesselMappingByVesselName(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("@vessel_name", SqlDbType.NVarChar).Value = vesselName;
                SqlDataReader rd = cmd.ExecuteReader();
                if (rd.HasRows)
                {
                    rd.Read();
                    vessel = new Vessel(false, rd);
                }
                rd.Close();
                //if vessel did not found in Mapping Table then get from softmar vessel master and store in vessel mapping table-----------
                if (vessel == null)
                {
                    cmd.CommandText = VesselSQL.GetSoftmarVesselMasterByName();
                    cmd.Connection = m_dataBaseConectivity.SoftmarSqlConnection;
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("@vessel_name", SqlDbType.NVarChar).Value = vesselName;
                    rd = cmd.ExecuteReader();
                    if (rd.HasRows)
                    {
                        rd.Read();
                        vessel = new Vessel(true, rd);
                    }
                    else
                    {
                       // throw new Exception("Can not Find Vessel in Softmar Vessel Master with name : " + vesselName + ".");
                        vessel = new Vessel();
                        vessel.vessel_name = vesselName;
                        vessel.vessel_code = "Can not found in Vessel Softmar Master";
                    }
                    rd.Close();
                    //if vessel found in Softmar Vessel master then store in mapping table------------
                    if (vessel != null)
                    {
                        //Insert vessel to vessel mapping table---------
                        cmd.CommandText = VesselSQL.InsertVesselMapping();
                        cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("@vessel_name", SqlDbType.NVarChar).Value = vessel.vessel_name;
                        cmd.Parameters.Add("@vessel_code", SqlDbType.NVarChar).Value = vessel.vessel_code;
                        cmd.Parameters.Add("@year_of_build", SqlDbType.NVarChar).Value = vessel.year_of_build;
                        cmd.Parameters.Add("@flag", SqlDbType.NVarChar).Value = vessel.flag;
                        cmd.Parameters.Add("@dwt", SqlDbType.NVarChar).Value = vessel.dwt;
                        cmd.Parameters.Add("@charter_type", SqlDbType.NVarChar).Value = vessel.charter_type;
                        cmd.Parameters.Add("@size_class", SqlDbType.NVarChar).Value = vessel.size_class;
                        cmd.Parameters.Add("@vessel_type", SqlDbType.NVarChar).Value = vessel.vessel_type;
                        cmd.Parameters.Add("@client", SqlDbType.NVarChar).Value = vessel.client;
                        cmd.Parameters.Add("@created_date", SqlDbType.DateTime).Value = DateTime.Now;
                        cmd.Parameters.Add("@modified_date", SqlDbType.DateTime).Value = DateTime.Now;
                        cmd.ExecuteNonQuery();
                        cmd.CommandText = VesselSQL.MaxVesselID();
                        cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                        cmd.Parameters.Clear();
                        vessel.vessel_id = Convert.ToInt32(cmd.ExecuteScalar());
                    }
                }
                // search base on softmar vessel name---
                agldimvalue agrVessel = m_agldimvalueService.GetAglDimValueVesselByDescription(vessel.vessel_name.Trim());
               
                if (agrVessel != null)
                {
                    vessel.agr_description = agrVessel.description;
                    vessel.agr_dim_value = agrVessel.dim_value;
                    vessel.agr_rel_value = agrVessel.rel_value;
                    vessel.client = agrVessel.client;
                    cmd.CommandText = VesselSQL.UpdateVesselAgressoMapping();
                    cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("@agr_description", SqlDbType.NVarChar).Value = vessel.agr_description;
                    cmd.Parameters.Add("@agr_dim_value", SqlDbType.NVarChar).Value = vessel.agr_dim_value;
                    cmd.Parameters.Add("@agr_rel_value", SqlDbType.NVarChar).Value = vessel.agr_rel_value;
                    cmd.Parameters.Add("@client", SqlDbType.NVarChar).Value = vessel.client;
                    cmd.Parameters.Add("@modified_date", SqlDbType.DateTime).Value = DateTime.Now;
                    cmd.Parameters.Add("@vessel_id", SqlDbType.Int).Value = vessel.vessel_id;
                    cmd.ExecuteNonQuery();
                }
                else 
                {   // search base on user description manual entry 
                    if (vessel.agr_description != string.Empty)
                    {
                        agrVessel = m_agldimvalueService.GetAglDimValueVesselByDescription(vessel.agr_description);
                        if (agrVessel != null)
                        {
                            vessel.agr_description = agrVessel.description;
                            vessel.agr_dim_value = agrVessel.dim_value;
                            vessel.agr_rel_value = agrVessel.rel_value;
                            vessel.client = agrVessel.client;
                            cmd.CommandText = VesselSQL.UpdateVesselAgressoMapping();
                            cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                            cmd.Parameters.Clear();
                            cmd.Parameters.Add("@agr_description", SqlDbType.NVarChar).Value = vessel.agr_description;
                            cmd.Parameters.Add("@agr_dim_value", SqlDbType.NVarChar).Value = vessel.agr_dim_value;
                            cmd.Parameters.Add("@agr_rel_value", SqlDbType.NVarChar).Value = vessel.agr_rel_value;
                            cmd.Parameters.Add("@client", SqlDbType.NVarChar).Value = vessel.client;
                            cmd.Parameters.Add("@modified_date", SqlDbType.DateTime).Value = DateTime.Now;
                            cmd.Parameters.Add("@vessel_id", SqlDbType.Int).Value = vessel.vessel_id;
                            cmd.ExecuteNonQuery();
                        }
                    }
                }
              
                return vessel;
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
                m_dataBaseConectivity.SoftmarSqlConnection.Close();
            }
            return vessel;
        }
        public bool AlertIfNotValidVessel(Vessel vessel, string key, AlertType alertType)
        {
            bool valid = false;
            if (vessel != null)
            {
                valid = vessel.IsValidVessel();
                if (valid)
                    m_alertService.CheckAndUpdateAlert(key, alertType, AlertStatus.RESOLVED, "Please update Agresso map for vessel with name : " + vessel.vessel_name, "Vessel");
                else
                    m_alertService.CheckAndUpdateAlert(key, alertType, AlertStatus.PENDING, "Please update Agresso map for vessel with name : " + vessel.vessel_name, "Vessel");
            }
            return valid;
        }
        public void UpdateVesselMapping(Vessel vessel)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = VesselSQL.UpdateVesselMapping();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.Parameters.Clear();
                cmd.Parameters.Add("@vessel_id", SqlDbType.Int).Value = vessel.vessel_id;
                cmd.Parameters.Add("@vessel_name", SqlDbType.NVarChar).Value = vessel.vessel_name;
                cmd.Parameters.Add("@vessel_code", SqlDbType.NVarChar).Value = vessel.vessel_code;
                cmd.Parameters.Add("@year_of_build", SqlDbType.NVarChar).Value = vessel.year_of_build;
                cmd.Parameters.Add("@flag", SqlDbType.NVarChar).Value = vessel.flag;
                cmd.Parameters.Add("@dwt", SqlDbType.NVarChar).Value = vessel.dwt;
                cmd.Parameters.Add("@charter_type", SqlDbType.NVarChar).Value = vessel.charter_type;
                cmd.Parameters.Add("@size_class", SqlDbType.NVarChar).Value = vessel.size_class;
                cmd.Parameters.Add("@vessel_type", SqlDbType.NVarChar).Value = vessel.vessel_type;
                cmd.Parameters.Add("@agr_description", SqlDbType.NVarChar).Value = vessel.agr_description;
                cmd.Parameters.Add("@agr_dim_value", SqlDbType.NVarChar).Value = vessel.agr_dim_value;
                cmd.Parameters.Add("@agr_rel_value", SqlDbType.NVarChar).Value = vessel.agr_rel_value;
                cmd.Parameters.Add("@modified_date", SqlDbType.NVarChar).Value = DateTime.Now;
                cmd.Parameters.Add("@client", SqlDbType.NVarChar).Value = vessel.client;
                cmd.Parameters.Add("@profit_centre_id", SqlDbType.Int).Value = vessel.ProfitCentre.profit_centre_id;
                cmd.ExecuteNonQuery();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        public void InsertVesselMapping(Vessel vessel)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = VesselSQL.FindVesselByCode();
                cmd.Parameters.Add("@vessel_code", SqlDbType.NVarChar).Value = vessel.vessel_code.Trim();
                int count = Convert.ToInt16(cmd.ExecuteScalar());
                if (count > 0) return;
                cmd.CommandText = VesselSQL.InsertVesselMapping();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.Parameters.Clear();
                cmd.Parameters.Add("@vessel_name", SqlDbType.NVarChar).Value = vessel.vessel_name;
                cmd.Parameters.Add("@vessel_code", SqlDbType.NVarChar).Value = vessel.vessel_code;
                cmd.Parameters.Add("@year_of_build", SqlDbType.NVarChar).Value = vessel.year_of_build;
                cmd.Parameters.Add("@flag", SqlDbType.NVarChar).Value = vessel.flag;
                cmd.Parameters.Add("@dwt", SqlDbType.NVarChar).Value = vessel.dwt;
                cmd.Parameters.Add("@charter_type", SqlDbType.NVarChar).Value = vessel.charter_type;
                cmd.Parameters.Add("@size_class", SqlDbType.NVarChar).Value = vessel.size_class;
                cmd.Parameters.Add("@agr_description", SqlDbType.NVarChar).Value = vessel.agr_description;
                cmd.Parameters.Add("@agr_dim_value", SqlDbType.NVarChar).Value = vessel.agr_dim_value;
                cmd.Parameters.Add("@agr_rel_value", SqlDbType.NVarChar).Value = vessel.agr_rel_value;
                cmd.Parameters.Add("@vessel_type", SqlDbType.NVarChar).Value = vessel.vessel_type;
                cmd.Parameters.Add("@client", SqlDbType.NVarChar).Value = vessel.client;
                cmd.Parameters.Add("@created_date", SqlDbType.DateTime).Value = DateTime.Now;
                cmd.Parameters.Add("@modified_date", SqlDbType.DateTime).Value = DateTime.Now;
                cmd.Parameters.Add("@profit_centre_id", SqlDbType.Int).Value = vessel.ProfitCentre.profit_centre_id;
                cmd.ExecuteNonQuery();
                cmd.CommandText = VesselSQL.MaxVesselID();
                cmd.Parameters.Clear();
                vessel.vessel_id = Convert.ToInt32(cmd.ExecuteScalar());
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        public IList GetAllVesselMapping(string p)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand(VesselSQL.GetAllVesselMappingLike(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("@vessel", SqlDbType.NVarChar).Value = p;
                SqlDataReader rd = cmd.ExecuteReader();
                if (rd.HasRows)
                {
                    while (rd.Read())
                    {
                        result.Add(new Vessel(false, rd));
                    }
                }
                rd.Close();
                return result;
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }
        public Vessel GetAllVesselMappingByDimValueAndClient(string dimvalue, string client)
        {
            Vessel result = null;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand(VesselSQL.GetAllVesselMappingByDimValueAndClient(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("@agr_dim_value", SqlDbType.NVarChar).Value = dimvalue;
                cmd.Parameters.Add("@client", SqlDbType.NVarChar).Value = client;
                SqlDataReader rd = cmd.ExecuteReader();
                if (rd.HasRows)
                {
                    while (rd.Read())
                    {
                        result = new Vessel(false, rd);
                    }
                }
                rd.Close();
                return result;
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }
        public IList FindVesselsByProfitCentreID(long p)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand(VesselSQL.GetAllVesselMappingByProfitCentreID(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("@profit_centre_id", SqlDbType.Int).Value = p;
                SqlDataReader rd = cmd.ExecuteReader();
                if (rd.HasRows)
                {
                    while (rd.Read())
                    {
                        result.Add(new Vessel(false, rd));
                    }
                }
                rd.Close();
                return result;
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }
        public IList FindVesselByName(string name, string vessel_sid)
        {
            try
            {
                m_dataBaseConectivity.SoftmarSqlConnection.Open();
                m_dataBaseConectivity.InterfaceSqlConnection.Open();

                Vessel vesselSoftmar = null;
                SqlCommand cmd = new SqlCommand(VesselSQL.GetSoftmarVesselMasterByID(), m_dataBaseConectivity.SoftmarSqlConnection);
                cmd.Parameters.Clear();
                cmd.Parameters.Add("@vessel_sid", SqlDbType.NVarChar).Value = vessel_sid;
                SqlDataReader rd = cmd.ExecuteReader();
                if (rd.HasRows)
                {
                    rd.Read();
                    vesselSoftmar = new Vessel(true, rd);
                    rd.Close();
                }
                else
                {
                    vesselSoftmar = new Vessel();
                    vesselSoftmar.vessel_name = name;
                    vesselSoftmar.vessel_code = "Can not found: " + name + " in softmar";
                    rd.Close();
                }
                IList result = new ArrayList();
                //-----------------------------------------------------------------------------------------------REVISION------------

                //cmd = new SqlCommand(VesselSQL.GetVesselMappingByVesselName(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd = new SqlCommand(VesselSQL.GetVesselMappingByVesselSID(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Clear();
                cmd.Parameters.Add("@vessel_sid", SqlDbType.NVarChar).Value = vesselSoftmar.vessel_sid;
                rd = cmd.ExecuteReader();

                IList vesselMapList = new ArrayList();
                while (rd.Read())
                {
                    vesselMapList.Add(new Vessel(false, rd));
                }
                rd.Close();

                if (vesselMapList.Count > 0)
                {   //found in Vessel MAP
                    foreach (Vessel vesselMap in vesselMapList)
                    {
                        //if old vessel-----------check agr value-------------
                        if (vesselMap.agr_dim_value == "")
                        {
                            IList vesselsInAgresso = m_agldimvalueService.GetAglDimValueVesselByDescriptionList(vesselMap.vessel_name.Trim());
                            if (vesselsInAgresso.Count > 0)
                            {
                                agldimvalue agrVessel = (agldimvalue)vesselsInAgresso[0];
                                vesselMap.agr_description = agrVessel.description;
                                vesselMap.agr_dim_value = agrVessel.dim_value;
                                vesselMap.agr_rel_value = agrVessel.rel_value;
                                vesselMap.client = agrVessel.client;
                                cmd.CommandText = VesselSQL.UpdateVesselAgressoMapping();
                                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                                cmd.Parameters.Clear();
                                cmd.Parameters.Add("@agr_description", SqlDbType.NVarChar).Value = vesselMap.agr_description;
                                cmd.Parameters.Add("@agr_dim_value", SqlDbType.NVarChar).Value = vesselMap.agr_dim_value;
                                cmd.Parameters.Add("@agr_rel_value", SqlDbType.NVarChar).Value = vesselMap.agr_rel_value;
                                cmd.Parameters.Add("@client", SqlDbType.NVarChar).Value = vesselMap.client;
                                cmd.Parameters.Add("@modified_date", SqlDbType.DateTime).Value = DateTime.Now;
                                cmd.Parameters.Add("@vessel_id", SqlDbType.Int).Value = vesselMap.vessel_id;
                                cmd.ExecuteNonQuery();
                                //update profitcentre base on rel_val-------------------
                                agldimvalue costCentre = m_agldimvalueService.GetAglDimValueCostCentreByDimVal(agrVessel.rel_value.Trim());
                                if (costCentre != null)
                                {
                                    ProfitCentre p = m_ProfitCentreService.GetProfitCentreByDimValue(costCentre.rel_value);
                                    if (p.profit_centre_id > 0)
                                    {
                                        cmd.CommandText = VesselSQL.UpdateVesselProfitCentreMapping();
                                        cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                                        cmd.Parameters.Clear();
                                        cmd.Parameters.Add("@vessel_id", SqlDbType.Int).Value = vesselMap.vessel_id;
                                        cmd.Parameters.Add("@profit_centre_id", SqlDbType.Int).Value = p.profit_centre_id;
                                        cmd.ExecuteNonQuery();
                                    }
                                }
                                //ProfitCentre p = m_ProfitCentreService.GetProfitCentreByDimValue(agrVessel.rel_value.Substring(1, 3));
                                //if (p.profit_centre_id > 0)
                                //{
                                //    cmd.CommandText = VesselSQL.UpdateVesselProfitCentreMapping();
                                //    cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                                //    cmd.Parameters.Clear();
                                //    cmd.Parameters.Add("@vessel_id", SqlDbType.Int).Value = vesselMap.vessel_id;
                                //    cmd.Parameters.Add("@profit_centre_id", SqlDbType.Int).Value = p.profit_centre_id;
                                //    cmd.ExecuteNonQuery();
                                //}
                            }
                        }
                    }
                }
                else
                {
                    IList agrVessels = m_agldimvalueService.GetAglDimValueVesselByDescriptionList(name.Trim());
                    agldimvalue agrVessel = agrVessels.Count > 0 ? (agldimvalue)agrVessels[0] : new agldimvalue();
                    //Insert vessel to vessel mapping table---------
                    cmd.CommandText = VesselSQL.InsertVesselMappingAgr();
                    cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                    cmd.Parameters.Clear();
                    vesselSoftmar.agr_description = agrVessel.description;
                    vesselSoftmar.agr_dim_value = agrVessel.dim_value;
                    vesselSoftmar.agr_rel_value = agrVessel.rel_value;
                    vesselSoftmar.client = agrVessel.client;
                    cmd.Parameters.Add("@vessel_name", SqlDbType.NVarChar).Value = vesselSoftmar.vessel_name;
                    cmd.Parameters.Add("@vessel_code", SqlDbType.NVarChar).Value = vesselSoftmar.vessel_code;
                    cmd.Parameters.Add("@year_of_build", SqlDbType.NVarChar).Value = vesselSoftmar.year_of_build;
                    cmd.Parameters.Add("@flag", SqlDbType.NVarChar).Value = vesselSoftmar.flag;
                    cmd.Parameters.Add("@dwt", SqlDbType.NVarChar).Value = vesselSoftmar.dwt;
                    cmd.Parameters.Add("@charter_type", SqlDbType.NVarChar).Value = vesselSoftmar.charter_type;
                    cmd.Parameters.Add("@size_class", SqlDbType.NVarChar).Value = vesselSoftmar.size_class;
                    cmd.Parameters.Add("@agr_description", SqlDbType.NVarChar).Value = agrVessel.description;
                    cmd.Parameters.Add("@agr_dim_value", SqlDbType.NVarChar).Value = agrVessel.dim_value;
                    cmd.Parameters.Add("@agr_rel_value", SqlDbType.NVarChar).Value = agrVessel.rel_value;
                    cmd.Parameters.Add("@vessel_type", SqlDbType.NVarChar).Value = vesselSoftmar.vessel_type;
                    cmd.Parameters.Add("@vessel_sid", SqlDbType.NVarChar).Value = vesselSoftmar.vessel_sid;
                    cmd.Parameters.Add("@lloyd_number", SqlDbType.NVarChar).Value = vesselSoftmar.lloyd_number;
                    cmd.Parameters.Add("@client", SqlDbType.NVarChar).Value = agrVessel.client;
                    cmd.Parameters.Add("@created_date", SqlDbType.DateTime).Value = DateTime.Now;
                    cmd.Parameters.Add("@modified_date", SqlDbType.DateTime).Value = DateTime.Now;
                    cmd.Parameters.Add("@profit_centre_id", SqlDbType.Int).Value = 0;

                    agldimvalue costCentre = m_agldimvalueService.GetAglDimValueCostCentreByDimVal(agrVessel.rel_value.Trim());
                    if (costCentre != null)
                    {
                        ProfitCentre p = m_ProfitCentreService.GetProfitCentreByDimValue(costCentre.rel_value);
                        if (p.profit_centre_id > 0)
                            cmd.Parameters["@profit_centre_id"].Value = p.profit_centre_id;
                    }
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = VesselSQL.MaxVesselID();
                    cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                    cmd.Parameters.Clear();
                    vesselSoftmar.vessel_id = Convert.ToInt32(cmd.ExecuteScalar());
                    vesselMapList.Add(vesselSoftmar);
                }
                //if new vessel---------------------------
                //IList agrVessels = m_agldimvalueService.GetAglDimValueVesselByDescriptionList(name.Trim());
                //foreach (agldimvalue agrVessel in agrVessels)
                //{
                //    bool found = false;
                //    foreach (Vessel vslmap in vesselMapList)
                //    {
                //        if (agrVessel.dim_value == vslmap.agr_dim_value)
                //        {
                //            found = true;
                //        }
                //    }
                //    if (!found)
                //    {
                //        //Insert vessel to vessel mapping table---------
                //        cmd.CommandText = VesselSQL.InsertVesselMappingAgr();
                //        cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                //        cmd.Parameters.Clear();
                //        vesselSoftmar.agr_description = agrVessel.description;
                //        vesselSoftmar.agr_dim_value = agrVessel.dim_value;
                //        vesselSoftmar.agr_rel_value = agrVessel.rel_value;
                //        vesselSoftmar.client = agrVessel.client;

                //        cmd.Parameters.Add("@vessel_name", SqlDbType.NVarChar).Value = vesselSoftmar.vessel_name;
                //        cmd.Parameters.Add("@vessel_code", SqlDbType.NVarChar).Value = vesselSoftmar.vessel_code;
                //        cmd.Parameters.Add("@year_of_build", SqlDbType.NVarChar).Value = vesselSoftmar.year_of_build;
                //        cmd.Parameters.Add("@flag", SqlDbType.NVarChar).Value = vesselSoftmar.flag;
                //        cmd.Parameters.Add("@dwt", SqlDbType.NVarChar).Value = vesselSoftmar.dwt;
                //        cmd.Parameters.Add("@charter_type", SqlDbType.NVarChar).Value = vesselSoftmar.charter_type;
                //        cmd.Parameters.Add("@size_class", SqlDbType.NVarChar).Value = vesselSoftmar.size_class;
                //        cmd.Parameters.Add("@agr_description", SqlDbType.NVarChar).Value = agrVessel.description;
                //        cmd.Parameters.Add("@agr_dim_value", SqlDbType.NVarChar).Value = agrVessel.dim_value;
                //        cmd.Parameters.Add("@agr_rel_value", SqlDbType.NVarChar).Value = agrVessel.rel_value;
                //        cmd.Parameters.Add("@vessel_type", SqlDbType.NVarChar).Value = vesselSoftmar.vessel_type;
                //        cmd.Parameters.Add("@vessel_sid", SqlDbType.NVarChar).Value = vesselSoftmar.vessel_id;
                //        cmd.Parameters.Add("@lloyd_number", SqlDbType.NVarChar).Value = vesselSoftmar.lloyd_number;
                //        cmd.Parameters.Add("@client", SqlDbType.NVarChar).Value = agrVessel.client;
                //        cmd.Parameters.Add("@created_date", SqlDbType.DateTime).Value = DateTime.Now;
                //        cmd.Parameters.Add("@modified_date", SqlDbType.DateTime).Value = DateTime.Now;
                //        cmd.Parameters.Add("@profit_centre_id", SqlDbType.Int).Value = 0;

                //        agldimvalue costCentre = m_agldimvalueService.GetAglDimValueCostCentreByDimVal(agrVessel.rel_value.Trim());
                //        if (costCentre != null)
                //        {
                //            ProfitCentre p = m_ProfitCentreService.GetProfitCentreByDimValue(costCentre.rel_value);
                //            if (p.profit_centre_id > 0)
                //                cmd.Parameters["@profit_centre_id"].Value = p.profit_centre_id;
                //        }
                //        cmd.ExecuteNonQuery();
                //        cmd.CommandText = VesselSQL.MaxVesselID();
                //        cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                //        cmd.Parameters.Clear();
                //        vesselSoftmar.vessel_id = Convert.ToInt32(cmd.ExecuteScalar());
                //        vesselMapList.Add(vesselSoftmar);
                //    }
                //}

             //   if (vesselMapList.Count > 0)
             //   {
                    //foreach (Vessel vesselMap in vesselMapList)
                    //{
                    //    bool agrAlready = vesselMap.agr_description != "" && vesselMap.client != "";
                    //    agldimvalue agrVessel = agrAlready ? m_agldimvalueService.GetAglDimValueVesselByDescription(vesselMap.agr_description, vesselMap.client.Trim())
                    //        : m_agldimvalueService.GetAglDimValueVesselByDescription(vesselMap.vessel_name);
                    //    if (agrVessel != null)
                    //    {
                    //        vesselMap.agr_description = agrVessel.description;
                    //        vesselMap.agr_dim_value = agrVessel.dim_value;
                    //        vesselMap.agr_rel_value = agrVessel.rel_value;
                    //        vesselMap.client = agrVessel.client;
                    //        cmd.CommandText = VesselSQL.UpdateVesselAgressoMapping();
                    //        cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                    //        cmd.Parameters.Clear();
                    //        cmd.Parameters.Add("@agr_description", SqlDbType.NVarChar).Value = vesselMap.agr_description;
                    //        cmd.Parameters.Add("@agr_dim_value", SqlDbType.NVarChar).Value = vesselMap.agr_dim_value;
                    //        cmd.Parameters.Add("@agr_rel_value", SqlDbType.NVarChar).Value = vesselMap.agr_rel_value;
                    //        cmd.Parameters.Add("@client", SqlDbType.NVarChar).Value = vesselMap.client;
                    //        cmd.Parameters.Add("@modified_date", SqlDbType.DateTime).Value = DateTime.Now;
                    //        cmd.Parameters.Add("@vessel_id", SqlDbType.Int).Value = vesselMap.vessel_id;
                    //        cmd.ExecuteNonQuery();
                    //        //update profitcentre base on rel_val-------------------

                    //        ProfitCentre p = m_ProfitCentreService.GetProfitCentreByDimValue(agrVessel.rel_value.Substring(1,3));
                    //        if (p.profit_centre_id > 0)
                    //        {
                    //            cmd.CommandText = VesselSQL.UpdateVesselProfitCentreMapping();
                    //            cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                    //            cmd.Parameters.Clear();
                    //            cmd.Parameters.Add("@vessel_id", SqlDbType.Int).Value = vesselMap.vessel_id;
                    //            cmd.Parameters.Add("@profit_centre_id", SqlDbType.Int).Value = p.profit_centre_id;
                    //            cmd.ExecuteNonQuery();
                    //        }
                    //    }
                    //    result.Add(vesselMap);
                    //}
              //  }
              //  else
              //  {
                //    agrVessels = m_agldimvalueService.GetAglDimValueVesselByDescriptionList(name.Trim());
                //    if (agrVessels.Count > 0)
                //    {
                //        foreach (agldimvalue vesagr in agrVessels)
                //        {
                //            //Insert vessel to vessel mapping table---------
                //            cmd.CommandText = VesselSQL.InsertVesselMappingAgr();
                //            cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                //            cmd.Parameters.Clear();
                //            cmd.Parameters.Add("@vessel_name", SqlDbType.NVarChar).Value = vesselSoftmar.vessel_name;
                //            cmd.Parameters.Add("@vessel_code", SqlDbType.NVarChar).Value = vesselSoftmar.vessel_code;
                //            cmd.Parameters.Add("@year_of_build", SqlDbType.NVarChar).Value = vesselSoftmar.year_of_build;
                //            cmd.Parameters.Add("@flag", SqlDbType.NVarChar).Value = vesselSoftmar.flag;
                //            cmd.Parameters.Add("@dwt", SqlDbType.NVarChar).Value = vesselSoftmar.dwt;
                //            cmd.Parameters.Add("@charter_type", SqlDbType.NVarChar).Value = vesselSoftmar.charter_type;
                //            cmd.Parameters.Add("@size_class", SqlDbType.NVarChar).Value = vesselSoftmar.size_class;
                //            cmd.Parameters.Add("@agr_description", SqlDbType.NVarChar).Value = vesagr.description;
                //            cmd.Parameters.Add("@agr_dim_value", SqlDbType.NVarChar).Value = vesagr.dim_value;
                //            cmd.Parameters.Add("@agr_rel_value", SqlDbType.NVarChar).Value = vesagr.rel_value;
                //            cmd.Parameters.Add("@vessel_type", SqlDbType.NVarChar).Value = vesselSoftmar.vessel_type;
                //            cmd.Parameters.Add("@client", SqlDbType.NVarChar).Value = vesagr.client;
                //            cmd.Parameters.Add("@created_date", SqlDbType.DateTime).Value = DateTime.Now;
                //            cmd.Parameters.Add("@modified_date", SqlDbType.DateTime).Value = DateTime.Now;
                //            cmd.Parameters.Add("@profit_centre_id", SqlDbType.Int).Value = 0;
                //            cmd.Parameters.Add("@vessel_sid", SqlDbType.NVarChar).Value = vesselSoftmar.vessel_id;
                //            cmd.Parameters.Add("@lloyd_number", SqlDbType.NVarChar).Value = vesselSoftmar.lloyd_number;

                //            agldimvalue costCentre = m_agldimvalueService.GetAglDimValueCostCentreByDimVal(vesagr.rel_value.Trim());
                //            if (costCentre != null)
                //            {
                //                ProfitCentre p = m_ProfitCentreService.GetProfitCentreByDimValue(costCentre.rel_value);
                //                if (p.profit_centre_id > 0)
                //                    cmd.Parameters["@profit_centre_id"].Value = p.profit_centre_id;
                //            }
                //            cmd.ExecuteNonQuery();
                //            cmd.CommandText = VesselSQL.MaxVesselID();
                //            cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                //            cmd.Parameters.Clear();
                //            vesselSoftmar.vessel_id = Convert.ToInt32(cmd.ExecuteScalar());
                //            result.Add(vesselSoftmar);
                //        }
                //    }
                //    else
                //    {
                //        rd.Close();
                //        cmd.CommandText = VesselSQL.InsertVesselMappingAgr();
                //        cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                //        cmd.Parameters.Clear();
                //        cmd.Parameters.Add("@vessel_name", SqlDbType.NVarChar).Value = vesselSoftmar.vessel_name.Trim();
                //        cmd.Parameters.Add("@vessel_code", SqlDbType.NVarChar).Value = vesselSoftmar.vessel_code.Trim();
                //        cmd.Parameters.Add("@year_of_build", SqlDbType.NVarChar).Value = vesselSoftmar.year_of_build.Trim();
                //        cmd.Parameters.Add("@flag", SqlDbType.NVarChar).Value = vesselSoftmar.flag.Trim().Length > 20 ? vesselSoftmar.flag.Trim().Substring(0, 20) : vesselSoftmar.flag.Trim();
                //        cmd.Parameters.Add("@dwt", SqlDbType.NVarChar).Value = vesselSoftmar.dwt.Trim();
                //        cmd.Parameters.Add("@charter_type", SqlDbType.NVarChar).Value = vesselSoftmar.charter_type.Trim();
                //        cmd.Parameters.Add("@size_class", SqlDbType.NVarChar).Value = vesselSoftmar.size_class;
                //        cmd.Parameters.Add("@agr_description", SqlDbType.NVarChar).Value = "";
                //        cmd.Parameters.Add("@agr_dim_value", SqlDbType.NVarChar).Value = "";
                //        cmd.Parameters.Add("@agr_rel_value", SqlDbType.NVarChar).Value = "";
                //        cmd.Parameters.Add("@vessel_type", SqlDbType.NVarChar).Value = "";
                //        cmd.Parameters.Add("@client", SqlDbType.NVarChar).Value = "";
                //        cmd.Parameters.Add("@created_date", SqlDbType.DateTime).Value = DateTime.Now;
                //        cmd.Parameters.Add("@modified_date", SqlDbType.DateTime).Value = DateTime.Now;
                //        cmd.Parameters.Add("@profit_centre_id", SqlDbType.Int).Value = 0;
                //        cmd.Parameters.Add("@vessel_sid", SqlDbType.NVarChar).Value = vesselSoftmar.vessel_id;
                //        cmd.Parameters.Add("@lloyd_number", SqlDbType.NVarChar).Value = vesselSoftmar.lloyd_number;
                //        cmd.ExecuteNonQuery();
                //        cmd.CommandText = VesselSQL.MaxVesselID();
                //        cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                //        cmd.Parameters.Clear();
                //        vesselSoftmar.vessel_id = Convert.ToInt32(cmd.ExecuteScalar());
                //        result.Add(vesselSoftmar);
                //    }
                //}

                //-------------------------------------------------------------------------------------------------

                //IList agrVessels = m_agldimvalueService.GetAglDimValueVesselByDescriptionList(name.Trim());
                //if (agrVessels.Count > 0)
                //{
                //    foreach (agldimvalue vesagr in agrVessels)
                //    {
                //        cmd = new SqlCommand(VesselSQL.GetVesselMappingByAgrDimvalNameAndClient(), m_dataBaseConectivity.InterfaceSqlConnection);
                //        cmd.Parameters.Clear();
                //        cmd.Parameters.Add("@agr_description", SqlDbType.NVarChar).Value = vesagr.description;
                //        cmd.Parameters.Add("@client", SqlDbType.NVarChar).Value = vesagr.client;
                //        cmd.Parameters.Add("@agr_dim_value", SqlDbType.NVarChar).Value = vesagr.dim_value;
                //        cmd.Parameters.Add("@agr_rel_value", SqlDbType.NVarChar).Value = vesagr.rel_value;
                //        rd = cmd.ExecuteReader();
                //        Vessel vesselMap = null;
                //        if (rd.HasRows)
                //        {
                //            rd.Read();
                //            vesselMap = new Vessel(false, rd);
                //            rd.Close();
                //            if (vesselMap.ProfitCentre.profit_centre_id == 0)//update profit centre if blank---------------try to search base on rel_val
                //            {
                //                agldimvalue costCentre = m_agldimvalueService.GetAglDimValueCostCentreByDimVal(vesselMap.agr_rel_value);
                //                if (costCentre != null)
                //                {
                //                    ProfitCentre p = m_ProfitCentreService.GetProfitCentreByDimValue(costCentre.rel_value);
                //                    if (p.profit_centre_id > 0)
                //                    {
                //                        cmd.CommandText = VesselSQL.UpdateVesselProfitCentreMapping();
                //                        cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                //                        cmd.Parameters.Clear();
                //                        cmd.Parameters.Add("@vessel_id", SqlDbType.Int).Value = vesselMap.vessel_id;
                //                        cmd.Parameters.Add("@profit_centre_id", SqlDbType.Int).Value = p.profit_centre_id;
                //                        cmd.ExecuteNonQuery();
                //                    }
                //                }
                //            }
                //            result.Add(vesselMap);
                //        }
                //        else
                //        {
                //            rd.Close();
                //            //Insert vessel to vessel mapping table---------
                //            cmd.CommandText = VesselSQL.InsertVesselMappingAgr();
                //            cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                //            cmd.Parameters.Clear();
                //            cmd.Parameters.Add("@vessel_name", SqlDbType.NVarChar).Value = vesselSoftmar.vessel_name;
                //            cmd.Parameters.Add("@vessel_code", SqlDbType.NVarChar).Value = vesselSoftmar.vessel_code;
                //            cmd.Parameters.Add("@year_of_build", SqlDbType.NVarChar).Value = vesselSoftmar.year_of_build;
                //            cmd.Parameters.Add("@flag", SqlDbType.NVarChar).Value = vesselSoftmar.flag;
                //            cmd.Parameters.Add("@dwt", SqlDbType.NVarChar).Value = vesselSoftmar.dwt;
                //            cmd.Parameters.Add("@charter_type", SqlDbType.NVarChar).Value = vesselSoftmar.charter_type;
                //            cmd.Parameters.Add("@size_class", SqlDbType.NVarChar).Value = vesselSoftmar.size_class;
                //            cmd.Parameters.Add("@agr_description", SqlDbType.NVarChar).Value = vesagr.description;
                //            cmd.Parameters.Add("@agr_dim_value", SqlDbType.NVarChar).Value = vesagr.dim_value;
                //            cmd.Parameters.Add("@agr_rel_value", SqlDbType.NVarChar).Value = vesagr.rel_value;
                //            cmd.Parameters.Add("@vessel_type", SqlDbType.NVarChar).Value = vesselSoftmar.vessel_type;
                //            cmd.Parameters.Add("@client", SqlDbType.NVarChar).Value = vesagr.client;
                //            cmd.Parameters.Add("@created_date", SqlDbType.DateTime).Value = DateTime.Now;
                //            cmd.Parameters.Add("@modified_date", SqlDbType.DateTime).Value = DateTime.Now;
                //            cmd.Parameters.Add("@profit_centre_id", SqlDbType.Int).Value = 0;

                //            agldimvalue costCentre = m_agldimvalueService.GetAglDimValueCostCentreByDimVal(vesagr.rel_value);
                //            if(costCentre != null)
                //            {
                //                ProfitCentre p = m_ProfitCentreService.GetProfitCentreByDimValue(costCentre.rel_value);
                //                if (p.profit_centre_id > 0)
                //                    cmd.Parameters["@profit_centre_id"].Value = p.profit_centre_id;
                //            }
                //            cmd.ExecuteNonQuery();
                //            cmd.CommandText = VesselSQL.MaxVesselID();
                //            cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                //            cmd.Parameters.Clear();
                //            vesselSoftmar.vessel_id = Convert.ToInt32(cmd.ExecuteScalar());
                //            result.Add(vesselSoftmar);
                //        }
                //    }
                //}
                //else
                //{
                //    cmd = new SqlCommand(VesselSQL.GetVesselMappingByVesselName(), m_dataBaseConectivity.InterfaceSqlConnection);
                //    cmd.Parameters.Clear();
                //    cmd.Parameters.Add("@vessel_name", SqlDbType.NVarChar).Value = vesselSoftmar.vessel_name;
                //    rd = cmd.ExecuteReader();
                //    Vessel vesselMap = null;
                //    if (rd.HasRows)
                //    {
                //        rd.Read();
                //        vesselMap = new Vessel(false, rd);
                //        result.Add(vesselMap);
                //        rd.Close();                    
                //        agldimvalue agrVessel = m_agldimvalueService.GetAglDimValueVesselByDescription(vesselMap.agr_description);
                //        if (agrVessel != null)
                //        {
                //            vesselMap.agr_description = agrVessel.description;
                //            vesselMap.agr_dim_value = agrVessel.dim_value;
                //            vesselMap.agr_rel_value = agrVessel.rel_value;
                //            vesselMap.client = agrVessel.client;
                //            cmd.CommandText = VesselSQL.UpdateVesselAgressoMapping();
                //            cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                //            cmd.Parameters.Clear();
                //            cmd.Parameters.Add("@agr_description", SqlDbType.NVarChar).Value = vesselMap.agr_description;
                //            cmd.Parameters.Add("@agr_dim_value", SqlDbType.NVarChar).Value = vesselMap.agr_dim_value;
                //            cmd.Parameters.Add("@agr_rel_value", SqlDbType.NVarChar).Value = vesselMap.agr_rel_value;
                //            cmd.Parameters.Add("@client", SqlDbType.NVarChar).Value = vesselMap.client;
                //            cmd.Parameters.Add("@modified_date", SqlDbType.DateTime).Value = DateTime.Now;
                //            cmd.Parameters.Add("@vessel_id", SqlDbType.Int).Value = vesselMap.vessel_id;
                //            cmd.ExecuteNonQuery();
                //            //update profitcentre base on rel_val-------------------
                //            ProfitCentre p = m_ProfitCentreService.GetProfitCentreByDimValue(vesselMap.agr_rel_value.Substring(1, 3));
                //            if (p.profit_centre_id > 0)
                //            {
                //                cmd.CommandText = VesselSQL.UpdateVesselProfitCentreMapping();
                //                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                //                cmd.Parameters.Clear();
                //                cmd.Parameters.Add("@vessel_id", SqlDbType.Int).Value = vesselMap.vessel_id;
                //                cmd.Parameters.Add("@profit_centre_id", SqlDbType.Int).Value = p.profit_centre_id;
                //                cmd.ExecuteNonQuery();
                //            }
                //        }
                //    }
                //    else
                //    {
                //        rd.Close();
                //        cmd.CommandText = VesselSQL.InsertVesselMappingAgr();
                //        cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                //        cmd.Parameters.Clear();
                //        cmd.Parameters.Add("@vessel_name", SqlDbType.NVarChar).Value = vesselSoftmar.vessel_name;
                //        cmd.Parameters.Add("@vessel_code", SqlDbType.NVarChar).Value = vesselSoftmar.vessel_code;
                //        cmd.Parameters.Add("@year_of_build", SqlDbType.NVarChar).Value = vesselSoftmar.year_of_build;
                //        cmd.Parameters.Add("@flag", SqlDbType.NVarChar).Value = vesselSoftmar.flag;
                //        cmd.Parameters.Add("@dwt", SqlDbType.NVarChar).Value = vesselSoftmar.dwt;
                //        cmd.Parameters.Add("@charter_type", SqlDbType.NVarChar).Value = vesselSoftmar.charter_type;
                //        cmd.Parameters.Add("@size_class", SqlDbType.NVarChar).Value = vesselSoftmar.size_class;
                //        cmd.Parameters.Add("@agr_description", SqlDbType.NVarChar).Value = "";
                //        cmd.Parameters.Add("@agr_dim_value", SqlDbType.NVarChar).Value = "";
                //        cmd.Parameters.Add("@agr_rel_value", SqlDbType.NVarChar).Value = "";
                //        cmd.Parameters.Add("@vessel_type", SqlDbType.NVarChar).Value = "";
                //        cmd.Parameters.Add("@client", SqlDbType.NVarChar).Value = "";
                //        cmd.Parameters.Add("@created_date", SqlDbType.DateTime).Value = DateTime.Now;
                //        cmd.Parameters.Add("@modified_date", SqlDbType.DateTime).Value = DateTime.Now;
                //        cmd.Parameters.Add("@profit_centre_id", SqlDbType.Int).Value = 0;
                //        cmd.ExecuteNonQuery();
                //        cmd.CommandText = VesselSQL.MaxVesselID();
                //        cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                //        cmd.Parameters.Clear();
                //        vesselSoftmar.vessel_id = Convert.ToInt32(cmd.ExecuteScalar());
                //        result.Add(vesselSoftmar);
                //    }
                //}
              
                return result;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.SoftmarSqlConnection.Close();
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }

        public IList FindVesselByName(string name, string vessel_sid, string profit_centre_fullname)
        {
            try
            {
                m_dataBaseConectivity.SoftmarSqlConnection.Open();
                m_dataBaseConectivity.InterfaceSqlConnection.Open();

                ProfitCentre profitCentre = m_ProfitCentreService.GetProfitCentreMapping(profit_centre_fullname);
                
                Vessel vesselSoftmar = null;
                SqlCommand cmd = new SqlCommand(VesselSQL.GetSoftmarVesselMasterByID(), m_dataBaseConectivity.SoftmarSqlConnection);
                cmd.Parameters.Clear();
                cmd.Parameters.Add("@vessel_sid", SqlDbType.NVarChar).Value = vessel_sid;
                SqlDataReader rd = cmd.ExecuteReader();
                if (rd.HasRows)
                {
                    rd.Read();
                    vesselSoftmar = new Vessel(true, rd);
                    rd.Close();
                }
                else
                {
                    vesselSoftmar = new Vessel();
                    vesselSoftmar.vessel_name = name;
                    vesselSoftmar.vessel_code = "Can not found: " + name + " in softmar";
                    rd.Close();
                }
                IList result = new ArrayList();
                //-----------------------------------------------------------------------------------------------REVISION------------

                //cmd = new SqlCommand(VesselSQL.GetVesselMappingByVesselName(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd = new SqlCommand(VesselSQL.GetVesselMappingByVesselSIDandProfitCentre(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Clear();
                cmd.Parameters.Add("@vessel_sid", SqlDbType.NVarChar).Value = vesselSoftmar.vessel_sid;
                cmd.Parameters.Add("@profit_centre_id", SqlDbType.NVarChar).Value = profitCentre.profit_centre_id;
                rd = cmd.ExecuteReader();

                IList vesselMapList = new ArrayList();
                while (rd.Read())
                {
                    vesselMapList.Add(new Vessel(false, rd));
                }
                rd.Close();

                if (vesselMapList.Count > 0)
                {   //found in Vessel MAP
                    foreach (Vessel vesselMap in vesselMapList)
                    {
                        //if old vessel-----------check agr value-------------
                        if (vesselMap.agr_dim_value == "")
                        {
                            IList vesselsInAgresso = m_agldimvalueService.GetAglDimValueVesselByDescriptionList(vesselMap.vessel_name.Trim());
                            if (vesselsInAgresso.Count > 0)
                            {
                                agldimvalue agrVessel = (agldimvalue)vesselsInAgresso[0];
                                vesselMap.agr_description = agrVessel.description;
                                vesselMap.agr_dim_value = agrVessel.dim_value;
                                vesselMap.agr_rel_value = agrVessel.rel_value;
                                vesselMap.client = agrVessel.client;
                                cmd.CommandText = VesselSQL.UpdateVesselAgressoMapping();
                                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                                cmd.Parameters.Clear();
                                cmd.Parameters.Add("@agr_description", SqlDbType.NVarChar).Value = vesselMap.agr_description;
                                cmd.Parameters.Add("@agr_dim_value", SqlDbType.NVarChar).Value = vesselMap.agr_dim_value;
                                cmd.Parameters.Add("@agr_rel_value", SqlDbType.NVarChar).Value = vesselMap.agr_rel_value;
                                cmd.Parameters.Add("@client", SqlDbType.NVarChar).Value = vesselMap.client;
                                cmd.Parameters.Add("@modified_date", SqlDbType.DateTime).Value = DateTime.Now;
                                cmd.Parameters.Add("@vessel_id", SqlDbType.Int).Value = vesselMap.vessel_id;
                                cmd.ExecuteNonQuery();
                                //update profitcentre base on rel_val-------------------
                                //agldimvalue costCentre = m_agldimvalueService.GetAglDimValueCostCentreByDimVal(agrVessel.rel_value.Trim());
                                //if (costCentre != null)
                                //{
                                //    ProfitCentre p = m_ProfitCentreService.GetProfitCentreByDimValue(costCentre.rel_value);
                                //    if (p.profit_centre_id > 0)
                                //    {
                                //        cmd.CommandText = VesselSQL.UpdateVesselProfitCentreMapping();
                                //        cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                                //        cmd.Parameters.Clear();
                                //        cmd.Parameters.Add("@vessel_id", SqlDbType.Int).Value = vesselMap.vessel_id;
                                //        cmd.Parameters.Add("@profit_centre_id", SqlDbType.Int).Value = p.profit_centre_id;
                                //        cmd.ExecuteNonQuery();
                                //    }
                                //}
                            }
                        }
                    }
                }
                else
                {
                    IList agrVessels = m_agldimvalueService.GetAglDimValueVesselByDescriptionList(name.Trim());
                    agldimvalue agrVessel = agrVessels.Count > 0 ? (agldimvalue)agrVessels[0] : new agldimvalue();
                    //Insert vessel to vessel mapping table---------
                    cmd.CommandText = VesselSQL.InsertVesselMappingAgr();
                    cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                    cmd.Parameters.Clear();
                    vesselSoftmar.agr_description = agrVessel.description;
                    vesselSoftmar.agr_dim_value = agrVessel.dim_value;
                    vesselSoftmar.agr_rel_value = agrVessel.rel_value;
                    vesselSoftmar.client = agrVessel.client;
                    cmd.Parameters.Add("@vessel_name", SqlDbType.NVarChar).Value = vesselSoftmar.vessel_name;
                    cmd.Parameters.Add("@vessel_code", SqlDbType.NVarChar).Value = vesselSoftmar.vessel_code;
                    cmd.Parameters.Add("@year_of_build", SqlDbType.NVarChar).Value = vesselSoftmar.year_of_build;
                    cmd.Parameters.Add("@flag", SqlDbType.NVarChar).Value = vesselSoftmar.flag;
                    cmd.Parameters.Add("@dwt", SqlDbType.NVarChar).Value = vesselSoftmar.dwt;
                    cmd.Parameters.Add("@charter_type", SqlDbType.NVarChar).Value = vesselSoftmar.charter_type;
                    cmd.Parameters.Add("@size_class", SqlDbType.NVarChar).Value = vesselSoftmar.size_class;
                    cmd.Parameters.Add("@agr_description", SqlDbType.NVarChar).Value = agrVessel.description;
                    cmd.Parameters.Add("@agr_dim_value", SqlDbType.NVarChar).Value = agrVessel.dim_value;
                    cmd.Parameters.Add("@agr_rel_value", SqlDbType.NVarChar).Value = agrVessel.rel_value;
                    cmd.Parameters.Add("@vessel_type", SqlDbType.NVarChar).Value = vesselSoftmar.vessel_type;
                    cmd.Parameters.Add("@vessel_sid", SqlDbType.NVarChar).Value = vesselSoftmar.vessel_sid;
                    cmd.Parameters.Add("@lloyd_number", SqlDbType.NVarChar).Value = vesselSoftmar.lloyd_number;
                    cmd.Parameters.Add("@client", SqlDbType.NVarChar).Value = agrVessel.client;
                    cmd.Parameters.Add("@created_date", SqlDbType.DateTime).Value = DateTime.Now;
                    cmd.Parameters.Add("@modified_date", SqlDbType.DateTime).Value = DateTime.Now;
                    cmd.Parameters.Add("@profit_centre_id", SqlDbType.Int).Value = profitCentre.profit_centre_id;

                    //agldimvalue costCentre = m_agldimvalueService.GetAglDimValueCostCentreByDimVal(agrVessel.rel_value.Trim());
                    //if (costCentre != null)
                    //{
                    //    ProfitCentre p = m_ProfitCentreService.GetProfitCentreByDimValue(costCentre.rel_value);
                    //    if (p.profit_centre_id > 0)
                    //        cmd.Parameters["@profit_centre_id"].Value = p.profit_centre_id;
                    //}
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = VesselSQL.MaxVesselID();
                    cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                    cmd.Parameters.Clear();
                    vesselSoftmar.vessel_id = Convert.ToInt32(cmd.ExecuteScalar());
                    vesselMapList.Add(vesselSoftmar);
                }
                return result;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.SoftmarSqlConnection.Close();
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }

        internal Vessel GetVesselMappingByProfitCentreIDAndVesselName(long profit_centre_id, string vessel_name, string client)
        {
            Vessel result = null;
            m_dataBaseConectivity.InterfaceSqlConnection.Open();
            try
            {
                SqlCommand cmd = new SqlCommand(VesselSQL.GetVesselMappingByProfitCentreIDAndVesselName(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("@vessel_name", SqlDbType.NVarChar).Value = vessel_name;
                cmd.Parameters.Add("@profit_centre_id", SqlDbType.NVarChar).Value = profit_centre_id;
                cmd.Parameters.Add("@client", SqlDbType.NVarChar).Value = client;
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result = new Vessel(false, rd);
                }
                rd.Close();
                return result;
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }
        public Vessel GetVesselMappingByDimVal(string dim_val)
        {
            Vessel result = null;
            m_dataBaseConectivity.InterfaceSqlConnection.Open();
            try
            {
                SqlCommand cmd = new SqlCommand(VesselSQL.GetVesselMappingByDimVal(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("@agr_dim_value", SqlDbType.NVarChar).Value = dim_val;
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result = new Vessel(false, rd);
                }
                rd.Close();
                return result;
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }

        public Vessel GetVesselMappingByVesselName(string vesselName)
        {
            Vessel result = null;
            m_dataBaseConectivity.InterfaceSqlConnection.Open();
            try
            {
                SqlCommand cmd = new SqlCommand(VesselSQL.GetVesselMappingByVesselName(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("@vessel_name", SqlDbType.NVarChar).Value = vesselName.Trim();
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result = new Vessel(false, rd);
                }
                rd.Close();
                return result;
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }

        public void DeleteVessel(Vessel a)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = VesselSQL.DeleteVesselMapping();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.Parameters.Add("@vessel_id", SqlDbType.NVarChar).Value = a.vessel_id;
                cmd.ExecuteNonQuery();
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }

        public void UpdateVesselIDLLOYDNumber()
        {
            try
            {
                IList vessels = new ArrayList();
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                m_dataBaseConectivity.SoftmarSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "select * from i_vessel_mapping";
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                    vessels.Add(new Vessel(false, rd));
                rd.Close();

                SqlCommand cmdSoftmar = new SqlCommand();
                cmdSoftmar.Connection = m_dataBaseConectivity.SoftmarSqlConnection;
                foreach (Vessel v in vessels)
                {
                    cmdSoftmar.CommandText = @"select  Vessel.ID vessel_sid,
                Vessel.LloydsNumber lloyd_number FROM VESSEL JOIN VAVESSELDATA ON VESSEL.ID = VAVESSELDATA.VESSEL WHERE VAVesselData.VesselCode = '" + v.vessel_code + "'";
                    rd = cmdSoftmar.ExecuteReader();
                    string vessel_sid = "";
                    string lloyds_number = "";
                    while (rd.Read())
                    {
                        vessel_sid = rd["vessel_sid"].ToString();
                        lloyds_number = rd["lloyd_number"].ToString();
                    }
                    rd.Close();

                    cmd.CommandText = string.Format("update i_vessel_mapping set vessel_sid='{0}', lloyd_number='{1}' where  vessel_id ={2}", vessel_sid, lloyds_number, v.vessel_id);
                    cmd.ExecuteNonQuery();
                }
                
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.SoftmarSqlConnection.Close();
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
    }
}
